
' -----------------------------------------------------------
' Hands-On 6-1
' Statements to be entered in the Immediate Window
' -----------------------------------------------------------

? ActiveCell.Value > 50
? ActiveCell.Value <= 50


' -----------------------------------------------------------
' Hands-On 6-2
' -----------------------------------------------------------

Sub SimpleIfThen()
    Dim weeks As String
    weeks = InputBox("How many weeks are in a year:", "Quiz")
    If weeks <> 52 Then MsgBox "Try Again"
End Sub


Sub SimpleIfThen() 'revised version
    Dim weeks As String
    On Error GoTo VeryEnd
    weeks = InputBox("How many weeks are in a year:", "Quiz")
    If weeks <> 52 Then MsgBox "Try Again": SimpleIfThen
    If weeks = 52 Then MsgBox "Congratulations!"
VeryEnd:
End Sub


' -----------------------------------------------------------
' Hands-On 6-3
' -----------------------------------------------------------

Sub IfThenAnd()
    Dim price As Single
    Dim units As Integer
    Dim rebate As Single

    Const strmsg1 = "To get a rebate you must buy an additional "
    Const strmsg2 = "Price must equal $7.00"

    units = Range("B1").Value
    price = Range("B2").Value

    If price = 7 And units >= 50 Then
        rebate = (price * units) * 0.1
        Range("A4").Value = "The rebate is: $" & rebate
    End If

    If price = 7 And units < 50 Then
        Range("A4").Value = strmsg1 & 50 - units & " unit(s)."
    End If

    If price <> 7 And units >= 50 Then
        Range("A4").Value = strmsg2
    End If

    If price <> 7 And units < 50 Then
        Range("A4").Value = "You didn't meet the criteria."
    End If
End Sub


' -----------------------------------------------------------
' Hands-On 6-4
' -----------------------------------------------------------

Sub WhatTypeOfDay()
    Dim response As String
    Dim question As String
    Dim strmsg1 As String, strmsg2 As String
    Dim myDate As Date

    question = "Enter any date in the format mm/dd/yyyy:" _
        & Chr(13) & " (e.g., 11/22/1999)"
        strmsg1 = "weekday"
        strmsg2 = "weekend"
        response = InputBox(question)
        myDate = Weekday(CDate(response))
        If myDate >= 2 And myDate <= 6 Then
            MsgBox strmsg1
        Else
            MsgBox strmsg2
        End If
End Sub


Sub EnterData()
    Dim cell As Object
    Dim strmsg As String

    On Error GoTo VeryEnd

    strmsg = "Select any cell:"
    Set cell = Application.InputBox(prompt:=strmsg, Type:=8)
    cell.Select

    If IsEmpty(ActiveCell) Then
        ActiveCell.Formula = InputBox("Enter text or number:")
    Else
        ActiveCell.Offset(1, 0).Select
    End If

VeryEnd:
End Sub


' -----------------------------------------------------------
' Hands-On 6-5
' -----------------------------------------------------------

Sub WhatValue()
    Range("A1").Select
    If ActiveCell.Value = 0 Then
        ActiveCell.Offset(0, 1).Value = "zero"
    ElseIf ActiveCell.Value > 0 Then
        ActiveCell.Offset(0, 1).Value = "positive"
    ElseIf ActiveCell.Value < 0 Then
        ActiveCell.Offset(0, 1).Value = "negative"
    End If
End Sub


Sub TestConditions()
    Range("A1").Select
    If IsEmpty(ActiveCell) Then
        MsgBox "The cell is empty."
    Else
        If IsNumeric(ActiveCell.Value) Then
            If ActiveCell.Value = 0 Then
                ActiveCell.Offset(0, 1).Value = "zero"
            ElseIf ActiveCell.Value > 0 Then
                ActiveCell.Offset(0, 1).Value = "positive"
            ElseIf ActiveCell.Value < 0 Then
                ActiveCell.Offset(0, 1).Value = "negative"
            End If
        Else
            ActiveCell.Offset(0, 1).Value = "text"
        End If
    End If
End Sub


' -----------------------------------------------------------
' Hands-On 6-6
' -----------------------------------------------------------

Sub TestButtons()
    Dim question As String
    Dim bts As Integer
    Dim myTitle As String
    Dim myButton As Integer

    question = "Do you want to open a new workbook?"
    bts = vbYesNoCancel + vbQuestion + vbDefaultButton1
    myTitle = "New Workbook"
    myButton = MsgBox(prompt:=question, _
                Buttons:=bts, _
                Title:=myTitle)
    Select Case myButton
        Case 6
            Workbooks.Add
        Case 7
            MsgBox "You can open a new book manually later."
        Case Else
            MsgBox "You pressed Cancel."
    End Select
End Sub


' -----------------------------------------------------------
' Hands-On 6-7
' -----------------------------------------------------------

Sub DisplayDiscount()
    Dim unitsSold As Integer
    Dim myDiscount As Single
    unitsSold = InputBox("Enter the number of sold units:")
    myDiscount = GetDiscount(unitsSold)
    MsgBox myDiscount
End Sub


Function GetDiscount(unitsSold As Integer)
    Select Case unitsSold
        Case 1 To 200
            GetDiscount = 0.05
        Case Is <= 500
            GetDiscount = 0.1
        Case 501 To 1000
            GetDiscount = 0.15
        Case Is > 1000
            GetDiscount = 0.2
    End Select
End Function

